Public Class frmBaoCaoKiemKeBangLoi

    Public Sub KhoiTaoBaoCaoKiemKe(ByVal loai As String)
        Dim slkk As String
        Dim hkk As String = "hkk1"

        slkk = loai
        If slkk = "slkk11" Then
            hkk = "hkk1"
        ElseIf slkk = "slkk17" Then
            hkk = "hkk7"
        End If

        Dim strsql As String
        strsql = "select dbo.LayTenDoiTuong(lk,'L') as lk,count(*) as so_mathang from tblTon where " & slkk & " >0 group by lk"
        Dim ds As DataSet = BaseDB.ExecSql_DataSet(strsql)
        dtgSoLuong.DataSource = ds.Tables(0)
        '
        strsql = "select dbo.LayTenDoiTuong(lk,'L') as lk,sum(gle*" & slkk & ") as giatri from tblTon where " & slkk & " >0 group by lk"
        ds = BaseDB.ExecSql_DataSet(strsql)
        dtgGiaTri.DataSource = ds.Tables(0)
        '
        strsql = "select dbo.LoaiThuoc(ma) as loaithuoc,sum(gle*" & slkk & ")as giatri from tblTon where lk = '1' and " & slkk & " >0 group by dbo.LoaiThuoc(ma)"
        ds = BaseDB.ExecSql_DataSet(strsql)
        dtgKhoThuongXuyen.DataSource = ds.Tables(0)
        '
        KhoiTaoFormatdtg()
        Dim t_hc_bb_hethan, tb_hethan, dc_hethan As Integer
        Dim giatri_thcbb, giatri_tb, giatri_dc As Double
        'thuoc het han
        ds = BaseDB.ExecSql_DataSet("select count (*),sum(gle*" & slkk & ") as giatri from tblTon where lk = '1' and (left(ma,3)= 'Y05' or left(ma,3)= 'Y06' or left(ma,3)= 'Y07' or left(ma,3)= 'Y08') and " & hkk & " > 2")
        t_hc_bb_hethan = ds.Tables(0).Rows(0).Item(0)
        giatri_thcbb = ds.Tables(0).Rows(0).Item(1)
        lblBaoCao.Text = "Trong kho Thường xuyên: " & Chr(10) & "Thuốc - Hóa Chất - Bông Băng có " & t_hc_bb_hethan & " khoản hết hạn với giá trị tiền là " & Format(giatri_thcbb, FormatMoneyStyle())
        'trang bi het han
        ds = BaseDB.ExecSql_DataSet("select count (*),sum(gle*" & slkk & ") as giatri from tblTon where lk = '1' and left(ma,3)= 'Y01' and " & hkk & "> 3")
        tb_hethan = ds.Tables(0).Rows(0).Item(0)
        giatri_tb = ds.Tables(0).Rows(0).Item(1)
        lblBaoCao.Text &= Chr(10) & "Trang Bị có " & tb_hethan & " khoản hết hạn với giá trị tiền là " & Format(giatri_tb, FormatMoneyStyle())
        'dung cu het han
        ds = BaseDB.ExecSql_DataSet("select count (*),sum(gle*" & slkk & ") as giatri from tblTon where lk = '1' and left(ma,3)= 'Y02' and " & hkk & " > 2")
        tb_hethan = ds.Tables(0).Rows(0).Item(0)
        giatri_tb = ds.Tables(0).Rows(0).Item(1)
        lblBaoCao.Text &= Chr(10) & "Dụng cụ có " & dc_hethan & " khoản hết hạn với giá trị tiền là " & Format(giatri_dc, FormatMoneyStyle())
    End Sub

    Public Sub KhoiTaoFormatdtg()
        With dtgSoLuong
            With .Columns.Item("lk")
                .HeaderText = "Loại Kho"
                .Width = "110"
            End With
            With .Columns.Item("so_mathang")
                .HeaderText = "Số mặt hàng"
                .Width = "110"
                .DefaultCellStyle.Format = FormatQuantityStyle()
            End With
        End With

        With dtgGiaTri
            With .Columns.Item("lk")
                .HeaderText = "Loại Kho"
                .Width = "110"
            End With
            With .Columns.Item("giatri")
                .HeaderText = "Giá Trị"
                .Width = "110"
                .DefaultCellStyle.Format = FormatMoneyStyle()
            End With
        End With

        With dtgKhoThuongXuyen
            With .Columns.Item("loaithuoc")
                .HeaderText = "Loại Thuốc"
                .Width = "130"
            End With
            With .Columns.Item("giatri")
                .HeaderText = "Giá Trị"
                .Width = "110"
                .DefaultCellStyle.Format = FormatMoneyStyle()
            End With
        End With

    End Sub

    Private Sub cbbKyKiemKe_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbbKyKiemKe.SelectedIndexChanged
        If (cbbKyKiemKe.SelectedIndex = 0) Then 'thang 1
            KhoiTaoBaoCaoKiemKe("slkk11")
        Else 'thang 7
            KhoiTaoBaoCaoKiemKe("slkk17")
        End If
    End Sub
End Class
